#!/bin/bash
APP=edu2077

if [ -n "$2" ] ;then
   do_date=$2
else 
   echo "请传入日期参数"
   exit
fi

dws_course_user_rate_1d="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table ${APP}.dws_course_user_rate_1d partition(dt)
select
course_id,
count(*)     wankesishu,
count(distinct (user_id))       wankerenshu,
dt
from
(
    select
           user_id,
       course_id,
      sum(play_sec)   shichang,
      --last_value(position_sec) over (partition by user_id order by position_sec desc) jindu
     max(position_sec) jindu,
dt
    from ${APP}.dwd_video_user_chapter_process_inc
    where dt='$do_date'
	group by user_id, course_id,dt
	)t1
where shichang>=810 and jindu>=810
group by course_id,dt;
"
dws_interaction_course_review_1d="
insert overwrite table ${APP}.dws_interaction_course_review_1d partition(dt)
select
    course_id,
    course_name,
    round(avg(review_stars),1) avg_score_1d,
    count(distinct user_id) user_num_1d,
    round(sum(if(review_stars=5,1,0)) / count(distinct user_id),2) positive_ratings_1d,
    dt
from
(
select distinct
    course_id,
    course_name,
    review_stars,
    user_id,
    dt
from
(
    select
        course_id,
        review_stars,
        user_id,
        dt
    from ${APP}.dwd_interaction_course_comment_inc
) t0
left join
(
    select
        id,
        course_name
    from ${APP}.dim_course_full
    where dt='$do_date'
) t2
on t0.course_id=t2.id
) t1
group by course_id, course_name,dt;
"
dws_trade_category_order_1d="
insert overwrite table ${APP}.dws_trade_category_order_1d partition(dt)
select
       category_id,
       category_name,
       count(distinct (order_id)) order_count_1d,
       count(distinct (user_id)) order_user_num_1d,
       sum(split_payment_amount) order_total_amount_1d,
       dt
from
(
    select
        course_id,
        order_id,
        user_id,
        split_payment_amount,
        dt
    from ${APP}.dwd_trade_pay_detail_suc_inc
)t1
left join
(
    select
        id,
        category_id,
        category_name
    from ${APP}.dim_course_full
    where dt='$do_date'
) t2
on t1.course_id = t2.id
group by category_id,category_name,dt;
"
dws_trade_course_order_1d="
insert overwrite table ${APP}.dws_trade_course_order_1d partition (dt)
select
    course_id,
    course_name,
    count(distinct (order_id)) order_count_1d,
    count(distinct (user_id)) order_user_num_1d,
    sum(split_payment_amount) order_total_amount_1d,
    dt
from
(
    select
        course_id,
        order_id,
        user_id,
        split_payment_amount,
        dt
    from ${APP}.dwd_trade_pay_detail_suc_inc
) t1
left join
(
    select
        id,
        course_name
    from ${APP}.dim_course_full
    where dt='$do_date'
) t2
on t1.course_id = t2.id
group by course_id,course_name,dt;
"

dws_trade_payment_1d="
insert overwrite table ${APP}.dws_trade_payment_1d partition(dt)
select
    count(distinct (user_id)) payment_count_1d,
    sum(course_num) payment_num_1d,
    sum(split_payment_amount) payment_amount_1d,
    dt
from ${APP}.dwd_trade_pay_detail_suc_inc
group by dt;
"
dws_trade_province_order_1d="
insert overwrite table ${APP}.dws_trade_province_order_1d partition(dt)
select
    province_id,
    province_name,
    area_code,
    iso_code,
    iso_3166_2,
    order_count_1d,
    order_user_num_1d,
    order_original_amount_1d,
    coupon_reduce_amount_1d,
    order_total_amount_1d,
    dt
from
(
    select
        province_id,
        count(distinct(order_id)) order_count_1d,
        count(distinct (user_id)) order_user_num_1d,
        sum(split_original_amount) order_original_amount_1d,
        sum(nvl(split_coupon_amount,0)) coupon_reduce_amount_1d,
        sum(split_payment_amount) order_total_amount_1d,
        dt
    from  ${APP}.dwd_trade_pay_detail_suc_inc
    group by province_id,dt
)o
left join
(
    select
        id,
        name province_name,
        area_code,
        iso_code,
        iso_3166_2
    from  ${APP}.dim_province_full
    where dt='$do_date'
)p
on o.province_id=p.id;
"

dws_trade_subject_order_1d="
insert overwrite table ${APP}.dws_trade_subject_order_1d partition(dt)
select
    subject_id,
    subject_name,
    count(distinct order_id) order_count_1d,
    count(distinct (user_id)) order_user_num_1d,
    sum(split_payment_amount) order_total_amount_1d,
    dt
from
(
    select
        course_id,
        order_id,
        user_id,
        split_payment_amount,
        dt
    from ${APP}.dwd_trade_pay_detail_suc_inc
)t1
left join
(
    select
        id,
        subject_id,
        subject_name
    from ${APP}.dim_course_full
    where dt='$do_date'
) t2
on t1.course_id=t2.id
group by subject_id, subject_name, dt;
"
dws_trade_user_cart_add_1d="
insert overwrite table ${APP}.dws_trade_user_cart_add_1d partition(dt)
select
    user_id,
    count(*),
    sum(1),
    dt
from ${APP}.dwd_trade_cart_add_inc
group by user_id,dt;
"
dws_trade_user_order_1d="
insert overwrite table ${APP}.dws_trade_user_order_1d partition(dt)
select
    user_id,
    count(distinct(order_id)) order_count_1d,
    sum(1) order_num_1d,
    sum(origin_amount) order_original_amount_1d,
    sum(final_amount) order_total_amount_1d,
    dt
from ${APP}.dwd_trade_order_detail_inc
group by user_id,dt;
"


case $1 in
    "dws_course_user_rate_1d" )
        hive -e "$dws_course_user_rate_1d"
    ;;
    "dws_interaction_course_review_1d" )
        hive -e "$dws_interaction_course_review_1d"
    ;;
    "dws_trade_category_order_1d" )
        hive -e "$dws_trade_category_order_1d"
    ;;
    "dws_trade_course_order_1d" )
        hive -e "$dws_trade_course_order_1d"
    ;;
    "dws_trade_payment_1d" )
        hive -e "$dws_trade_payment_1d"
    ;;
    "dws_trade_province_order_1d" )
        hive -e "$dws_trade_province_order_1d"
    ;;
    "dws_trade_subject_order_1d" )
        hive -e "$dws_trade_subject_order_1d"
    ;;
    "dws_trade_user_cart_add_1d" )
        hive -e "$dws_trade_user_cart_add_1d"
    ;;
    "dws_trade_user_order_1d" )
        hive -e "$dws_trade_user_order_1d"
    ;;
    "all" )
        hive -e "$dws_course_user_rate_1d$dws_interaction_course_review_1d$dws_trade_category_order_1d$dws_trade_course_order_1d$dws_trade_payment_1d$dws_trade_province_order_1d$dws_trade_subject_order_1d$dws_trade_user_cart_add_1d$dws_trade_user_order_1d"
    ;;
esac

